{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import codecs\n",
    "import csv\n",
    "import time\n",
    "import os\n",
    "import re\n",
    "import gzip\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH_TO_DATA = 'Data/'\n",
    "PATH_TO_DATA_EN = PATH_TO_DATA+\"enwiki/\"\n",
    "PATH_TO_DATA_UK = PATH_TO_DATA+\"ukwiki/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "ENWIKI_ART_FNMS = []\n",
    "for file in os.listdir(PATH_TO_DATA_EN):\n",
    "    if re.match(r\"enwiki-20180620-pages-meta-current\\d{2}-p\\d+p\\d+.xml_art.csv.gz\", file):\n",
    "        ENWIKI_ART_FNMS.append(file)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def unpack(file_name):\n",
    "    file_name_new = file_name.replace(\".gz\",\"\")\n",
    "    with gzip.open(file_name, 'rb') as f_in, open(file_name_new, 'wb') as f_out:\n",
    "        f_out.writelines(f_in)\n",
    "    return file_name_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def pack_and_remove(file_name):\n",
    "    file_name_new = file_name+'.gz'\n",
    "    with open(file_name, 'rb') as f_in, gzip.open(file_name_new, 'wb') as f_out:\n",
    "        f_out.writelines(f_in)\n",
    "    os.remove(file_name)\n",
    "    return file_name_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data/enwiki/enwiki-20180620-pages-meta-current01-p10p30303.xml_art.csv.gz\n",
      "df_title_count size: (26591, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current02-p30304p88444.xml_art.csv.gz\n",
      "df_title_count size: (72724, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current03-p88445p200507.xml_art.csv.gz\n",
      "df_title_count size: (145838, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current04-p200511p352689.xml_art.csv.gz\n",
      "df_title_count size: (244592, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current05-p352690p565313.xml_art.csv.gz\n",
      "df_title_count size: (366777, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current06-p565314p892912.xml_art.csv.gz\n",
      "df_title_count size: (489754, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current07-p892914p1268691.xml_art.csv.gz\n",
      "df_title_count size: (629010, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current08-p1268692p1791079.xml_art.csv.gz\n",
      "df_title_count size: (783272, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current09-p1791080p2336422.xml_art.csv.gz\n",
      "df_title_count size: (940664, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current10-p2336425p3046511.xml_art.csv.gz\n",
      "df_title_count size: (1107917, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current11-p3046514p3926861.xml_art.csv.gz\n",
      "df_title_count size: (1297859, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current12-p3926863p5040436.xml_art.csv.gz\n",
      "df_title_count size: (1478541, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current13-p5040438p6197594.xml_art.csv.gz\n",
      "df_title_count size: (1685488, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current14-p6197598p7697598.xml_art.csv.gz\n",
      "df_title_count size: (1854726, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current14-p7697598p7744799.xml_art.csv.gz\n",
      "df_title_count size: (1860517, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current15-p7744803p9244803.xml_art.csv.gz\n",
      "df_title_count size: (2029968, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current15-p9244803p9518048.xml_art.csv.gz\n",
      "df_title_count size: (2055380, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current16-p11018050p11539266.xml_art.csv.gz\n",
      "df_title_count size: (2105350, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current16-p9518050p11018050.xml_art.csv.gz\n",
      "df_title_count size: (2256123, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current17-p11539268p13039268.xml_art.csv.gz\n",
      "df_title_count size: (2441705, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current17-p13039268p13693071.xml_art.csv.gz\n",
      "df_title_count size: (2498519, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current18-p13693074p15193074.xml_art.csv.gz\n",
      "df_title_count size: (2647421, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current18-p15193074p16120542.xml_art.csv.gz\n",
      "df_title_count size: (2718577, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current19-p16120543p17620543.xml_art.csv.gz\n",
      "df_title_count size: (2818404, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current19-p17620543p18754735.xml_art.csv.gz\n",
      "df_title_count size: (2903942, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current20-p18754736p20254736.xml_art.csv.gz\n",
      "df_title_count size: (3019488, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current20-p20254736p21222156.xml_art.csv.gz\n",
      "df_title_count size: (3091470, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current21-p21222158p22722158.xml_art.csv.gz\n",
      "df_title_count size: (3240174, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current21-p22722158p23927983.xml_art.csv.gz\n",
      "df_title_count size: (3353051, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current22-p23927984p25427984.xml_art.csv.gz\n",
      "df_title_count size: (3510451, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current22-p25427984p26823660.xml_art.csv.gz\n",
      "df_title_count size: (3627345, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current23-p26823661p28323661.xml_art.csv.gz\n",
      "df_title_count size: (3757806, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current23-p28323661p29823661.xml_art.csv.gz\n",
      "df_title_count size: (3869645, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current23-p29823661p30503449.xml_art.csv.gz\n",
      "df_title_count size: (3912480, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current24-p30503451p32003451.xml_art.csv.gz\n",
      "df_title_count size: (4054509, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current24-p32003451p33503451.xml_art.csv.gz\n",
      "df_title_count size: (4206628, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current24-p33503451p33952815.xml_art.csv.gz\n",
      "df_title_count size: (4248931, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current25-p33952816p35452816.xml_art.csv.gz\n",
      "df_title_count size: (4348495, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current25-p35452816p36952816.xml_art.csv.gz\n",
      "df_title_count size: (4468770, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current25-p36952816p38067202.xml_art.csv.gz\n",
      "df_title_count size: (4547418, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current26-p38067203p39567203.xml_art.csv.gz\n",
      "df_title_count size: (4656529, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current26-p39567203p41067203.xml_art.csv.gz\n",
      "df_title_count size: (4767757, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current26-p41067203p42567203.xml_art.csv.gz\n",
      "df_title_count size: (4874421, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current26-p42567203p42663461.xml_art.csv.gz\n",
      "df_title_count size: (4880946, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current27-p42663462p44163462.xml_art.csv.gz\n",
      "df_title_count size: (4997218, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current27-p44163462p45663462.xml_art.csv.gz\n",
      "df_title_count size: (5098687, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current27-p45663462p47163462.xml_art.csv.gz\n",
      "df_title_count size: (5166637, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current27-p47163462p48663462.xml_art.csv.gz\n",
      "df_title_count size: (5256006, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current27-p48663462p50163462.xml_art.csv.gz\n",
      "df_title_count size: (5342952, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current27-p50163462p51663462.xml_art.csv.gz\n",
      "df_title_count size: (5437555, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current27-p51663462p53163462.xml_art.csv.gz\n",
      "df_title_count size: (5511370, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current27-p53163462p54663462.xml_art.csv.gz\n",
      "df_title_count size: (5594663, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current27-p54663462p56163462.xml_art.csv.gz\n",
      "df_title_count size: (5696545, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current27-p56163462p57663462.xml_art.csv.gz\n",
      "df_title_count size: (5807967, 2)\n",
      "Data/enwiki/enwiki-20180620-pages-meta-current27-p57663462p57726175.xml_art.csv.gz\n",
      "df_title_count size: (5810884, 2)\n"
     ]
    }
   ],
   "source": [
    "df_title_count = None\n",
    "for fn in ENWIKI_ART_FNMS:\n",
    "    fn = PATH_TO_DATA_EN+fn\n",
    "    print(fn)\n",
    "    fn_new = unpack(fn)\n",
    "    df_articles = pd.read_csv(fn_new, encoding='ISO-8859-1',quotechar=\"'\")\n",
    "\n",
    "    df_red_links = df_articles[df_articles['is_red_link'] == True]\n",
    "    df_title_count_tmp = pd.DataFrame(df_red_links.groupby('link_val').link_val.count())\n",
    "    df_title_count_tmp.columns = ['link']\n",
    "    df_title_count_tmp = df_title_count_tmp.reset_index(col_level=1)\n",
    "    df_title_count_tmp.columns = ['link_title', 'in_count']\n",
    "    \n",
    "    if df_title_count is not None:\n",
    "        df_title_count = df_title_count.append(df_title_count_tmp)        \n",
    "        #print(\"append\")\n",
    "    else:\n",
    "        df_title_count = df_title_count_tmp\n",
    "        #print(\"assign\")    \n",
    "    df_title_count = df_title_count.groupby('link_title').in_count.sum().copy()\n",
    "    df_title_count.columns = ['link']\n",
    "    df_title_count = df_title_count.reset_index()\n",
    "    df_title_count.columns = ['link_title', 'in_count']        \n",
    "    print(\"df_title_count size: {}\".format(df_title_count.shape))\n",
    "    os.remove(fn_new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_title_count = df_title_count.sort_values(['in_count'], ascending=[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_title_count.to_csv(PATH_TO_DATA_EN+'enwiki-20180620-red_name_count.csv', index = False, encoding='ISO-8859-1', quotechar=\"'\", escapechar =\"\\\\\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_title_count = pd.read_csv(PATH_TO_DATA_EN+'enwiki-20180620-red_name_count.csv', encoding='ISO-8859-1', quotechar=\"'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>link_title</th>\n",
       "      <th>in_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>List of South Korean films of 19xx</td>\n",
       "      <td>4451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Poll</td>\n",
       "      <td>1547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Philippine general election,</td>\n",
       "      <td>1463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>List of Quebec provincial electoral districts</td>\n",
       "      <td>1295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Saaremaa Parish</td>\n",
       "      <td>841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>INGEOMINAS</td>\n",
       "      <td>569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Airport Name1</td>\n",
       "      <td>444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ã©pÃ©e</td>\n",
       "      <td>430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Technical Sports Racing</td>\n",
       "      <td>385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>OurSportsCentral.com</td>\n",
       "      <td>377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Iran Daylight Time</td>\n",
       "      <td>371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>District</td>\n",
       "      <td>367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>State/Province</td>\n",
       "      <td>340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Association_football_positions</td>\n",
       "      <td>316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Âµm</td>\n",
       "      <td>302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Conny Kissling</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>GameZone</td>\n",
       "      <td>298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Pocono ARCA 200</td>\n",
       "      <td>294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Ã©migrÃ©</td>\n",
       "      <td>287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Ng Chiu Kok</td>\n",
       "      <td>287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Wilson JosÃ© Eduardo Moreira da Costa</td>\n",
       "      <td>279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Producer name</td>\n",
       "      <td>277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Winchester ARCA 200</td>\n",
       "      <td>273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Director name</td>\n",
       "      <td>266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>LÃ¤Ã¤neranna Parish</td>\n",
       "      <td>263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Songwriter name</td>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Primera Nacional de Rugby</td>\n",
       "      <td>257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Pennsylvania 200</td>\n",
       "      <td>254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Brian Colvin (referee)</td>\n",
       "      <td>251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>OPS (satellite)</td>\n",
       "      <td>247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810854</th>\n",
       "      <td>Hawthorne, California</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810855</th>\n",
       "      <td>Hawthorne St. Records</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810856</th>\n",
       "      <td>Hawthorne Sprint Handicap</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810857</th>\n",
       "      <td>Hawthorne Sound</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810858</th>\n",
       "      <td>Hawthorne Society</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810859</th>\n",
       "      <td>Hawthorne School of Art</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810860</th>\n",
       "      <td>Hawthorne School (Beverly Hills, California)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810861</th>\n",
       "      <td>Hawthorne Racing Hall of Fame</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810862</th>\n",
       "      <td>Hawthorne Racecourse</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810863</th>\n",
       "      <td>Hawthorne Q. Mills</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810864</th>\n",
       "      <td>Hawthorne Public School District</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810865</th>\n",
       "      <td>Hawthorne, Ontario</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810866</th>\n",
       "      <td>Hawthorne, Ottawa</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810867</th>\n",
       "      <td>Hawthorne, South Carolina</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810868</th>\n",
       "      <td>Hawthorneachiton</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810869</th>\n",
       "      <td>Hawtree Limited</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810870</th>\n",
       "      <td>Hawtree Creek Bridge</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810871</th>\n",
       "      <td>Hawtons, 5 Castle Street</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810872</th>\n",
       "      <td>Hawtin Raydar</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810873</th>\n",
       "      <td>Hawthrone School (Missoula, Montana)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810874</th>\n",
       "      <td>Hawthorns, Staffordshire</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810875</th>\n",
       "      <td>Hawthorne_Caballeros_Drum_and_Bugle_Corps</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810876</th>\n",
       "      <td>Hawthorne, Tennessee</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810877</th>\n",
       "      <td>Hawthorne_(season_1)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810878</th>\n",
       "      <td>Hawthorne-Riemenschneider (crater)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810879</th>\n",
       "      <td>Hawthorne-Cowart House</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810880</th>\n",
       "      <td>Hawthorne-Cedar Knolls Union Free School District</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810881</th>\n",
       "      <td>Hawthorne,_Philadelphia</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810882</th>\n",
       "      <td>Hawthorne, Virginia</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5810883</th>\n",
       "      <td>ð«</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5810884 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                link_title  in_count\n",
       "0                       List of South Korean films of 19xx      4451\n",
       "1                                                 The Poll      1547\n",
       "2                            Philippine general election,       1463\n",
       "3            List of Quebec provincial electoral districts      1295\n",
       "4                                          Saaremaa Parish       841\n",
       "5                                               INGEOMINAS       569\n",
       "6                                            Airport Name1       444\n",
       "7                                                   Ã©pÃ©e       430\n",
       "8                                  Technical Sports Racing       385\n",
       "9                                     OurSportsCentral.com       377\n",
       "10                                     Iran Daylight Time        371\n",
       "11                                                District       367\n",
       "12                                          State/Province       340\n",
       "13                          Association_football_positions       316\n",
       "14                                                     Âµm       302\n",
       "15                                          Conny Kissling       300\n",
       "16                                                GameZone       298\n",
       "17                                         Pocono ARCA 200       294\n",
       "18                                                Ã©migrÃ©       287\n",
       "19                                             Ng Chiu Kok       287\n",
       "20                   Wilson JosÃ© Eduardo Moreira da Costa       279\n",
       "21                                           Producer name       277\n",
       "22                                     Winchester ARCA 200       273\n",
       "23                                           Director name       266\n",
       "24                                     LÃ¤Ã¤neranna Parish       263\n",
       "25                                         Songwriter name       260\n",
       "26                               Primera Nacional de Rugby       257\n",
       "27                                        Pennsylvania 200       254\n",
       "28                                  Brian Colvin (referee)       251\n",
       "29                                         OPS (satellite)       247\n",
       "...                                                    ...       ...\n",
       "5810854                             Hawthorne, California          1\n",
       "5810855                              Hawthorne St. Records         1\n",
       "5810856                          Hawthorne Sprint Handicap         1\n",
       "5810857                                    Hawthorne Sound         1\n",
       "5810858                                  Hawthorne Society         1\n",
       "5810859                            Hawthorne School of Art         1\n",
       "5810860       Hawthorne School (Beverly Hills, California)         1\n",
       "5810861                      Hawthorne Racing Hall of Fame         1\n",
       "5810862                               Hawthorne Racecourse         1\n",
       "5810863                                 Hawthorne Q. Mills         1\n",
       "5810864                   Hawthorne Public School District         1\n",
       "5810865                                 Hawthorne, Ontario         1\n",
       "5810866                                  Hawthorne, Ottawa         1\n",
       "5810867                          Hawthorne, South Carolina         1\n",
       "5810868                                   Hawthorneachiton         1\n",
       "5810869                                    Hawtree Limited         1\n",
       "5810870                               Hawtree Creek Bridge         1\n",
       "5810871                           Hawtons, 5 Castle Street         1\n",
       "5810872                                      Hawtin Raydar         1\n",
       "5810873               Hawthrone School (Missoula, Montana)         1\n",
       "5810874                           Hawthorns, Staffordshire         1\n",
       "5810875          Hawthorne_Caballeros_Drum_and_Bugle_Corps         1\n",
       "5810876                               Hawthorne, Tennessee         1\n",
       "5810877                               Hawthorne_(season_1)         1\n",
       "5810878                 Hawthorne-Riemenschneider (crater)         1\n",
       "5810879                             Hawthorne-Cowart House         1\n",
       "5810880  Hawthorne-Cedar Knolls Union Free School District         1\n",
       "5810881                            Hawthorne,_Philadelphia         1\n",
       "5810882                                Hawthorne, Virginia         1\n",
       "5810883                                               ð«         1\n",
       "\n",
       "[5810884 rows x 2 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_title_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>in_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>5.810884e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.667161e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>3.457072e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4.451000e+03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           in_count\n",
       "count  5.810884e+06\n",
       "mean   1.667161e+00\n",
       "std    3.457072e+00\n",
       "min    1.000000e+00\n",
       "25%    1.000000e+00\n",
       "50%    1.000000e+00\n",
       "75%    2.000000e+00\n",
       "max    4.451000e+03"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_title_count.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_count_by_count = pd.DataFrame(df_title_count.groupby('in_count').in_count.count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_count_by_count.columns = ['link']\n",
    "df_count_by_count = df_count_by_count.reset_index()\n",
    "df_count_by_count.columns = ['count', 'in_count']  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_count_by_count = df_count_by_count.sort_values(['count'], ascending=[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>in_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>4354094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>811612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>267891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>129747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>69636</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count  in_count\n",
       "0      1   4354094\n",
       "1      2    811612\n",
       "2      3    267891\n",
       "3      4    129747\n",
       "4      5     69636"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_count_by_count.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>in_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>229.000000</td>\n",
       "      <td>2.290000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>166.930131</td>\n",
       "      <td>2.537504e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>337.539282</td>\n",
       "      <td>2.929486e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>58.000000</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>115.000000</td>\n",
       "      <td>7.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>181.000000</td>\n",
       "      <td>7.100000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4451.000000</td>\n",
       "      <td>4.354094e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             count      in_count\n",
       "count   229.000000  2.290000e+02\n",
       "mean    166.930131  2.537504e+04\n",
       "std     337.539282  2.929486e+05\n",
       "min       1.000000  1.000000e+00\n",
       "25%      58.000000  1.000000e+00\n",
       "50%     115.000000  7.000000e+00\n",
       "75%     181.000000  7.100000e+01\n",
       "max    4451.000000  4.354094e+06"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_count_by_count.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_count_by_count.to_csv(PATH_TO_DATA_EN+'enwiki-20180620-red_count_by_count.csv', index = False, encoding='ISO-8859-1', quotechar=\"'\", escapechar =\"\\\\\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
